drop table jms_dwd.dwd_scan_small_equipment_hi;
CREATE TABLE dwd_scan_small_equipment_hi (
    scan_time_dt               date comment '扫描时间yyyy-mm-dd',
    scan_time_hh               varchar(50) comment '扫描时间所属小时hh',
    equipment_code             varchar(50) comment '交叉带',
    supply_desk_code           varchar(50) comment '供包台',
   center_code                 varchar(50) comment '中心id',
   center_name                 varchar(50) comment '中心',
   agent_code                  varchar(50) comment '代理区id',
   agent_name                  varchar(50) comment '代理区',
   inductions_num              bigint comment '供件量/操作量',
   valid_sorting_num           bigint comment '有效分拣量',
   error_num                   bigint comment '异常量',
   out_package_num             bigint comment '出港建包',
   out_sorting_num             bigint comment '出港分拣',
   in_package_num              bigint comment '进港建包',
   in_sorting_num              bigint comment '进港分拣',
   package_num                 bigint comment '建包量',
   inductions_total            bigint comment '供包台去重',
   supply_valid                bigint comment '供包台有效时长',
   equipment_valid             bigint comment '交叉带有效时长',
   center_valid                bigint comment '中心有效时长',
   agent_valid                 bigint comment '代理区有效时长',
   all_valid                   bigint comment '全国有效时长'
) ENGINE=OLAP
DUPLICATE KEY(`scan_time_dt`,`scan_time_hh`)
COMMENT '智兔APP交叉带数据'
PARTITION BY RANGE (scan_time_dt) (
    START ("2022-07-01") END ("2022-08-25") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(scan_time_dt,scan_time_hh) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);

